// Created by Windward Studios - no copyright is claimed. This code can be used in
// any manner by anyone for any reason. There is no copyright of any kind on it. You may
// use it in commercial products. You may change it without sharing those changes.
// We ask that you keep the "created by Windward Studios" in a comment at the top.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using Kailua.net.windward.utils;

namespace net.windward.utils.ado.DB2
{
    /// <summary>
    /// A DB2 database.
    /// </summary>
    public class WrDB2Database : IWrDatabase
    {
        private readonly string server;
        private readonly string database;
        private readonly DbProviderFactory provider;
        private readonly WrDB2Syntax syntax;
        private readonly WrCredentials credentials;

		private string schema;
		private bool gotSchema;

		private WrTable[] tables;
		private WrView[] views;
		private WrStoredProcedure[] procedures;

        private static readonly Hashtable types;

        static WrDB2Database()
        {
            types = new Hashtable();

            //Data types taken from ibm website (http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0008483.htm)

            //Extensible Markup Language:
            types.Add("xml", DbType.Xml);
            //String->Character:Fixed Length (1-254 characters long)
            types.Add("char", DbType.String);
            types.Add("character", DbType.String);
            //String->Character:Varying Length
            types.Add("varchar", DbType.String);
            //Signed Numeric->Exact->Decimal: Packed
            types.Add("decimal", DbType.Decimal);
            //Signed Numeric->Exact->Binary Integer: 16-bit, 32-bit, 64-bit
            types.Add("smallint", DbType.Int16);
            types.Add("integer", DbType.Int32);
            types.Add("bigint", DbType.Int64);
            //Signed Numeric->Approximate->floating point: single precision, double precision
            types.Add("real", DbType.Single);
            types.Add("double", DbType.Double);
            //Datetime: time,timestamp,date
            types.Add("date", DbType.Date);
            types.Add("time", DbType.Time);
            types.Add("timestamp", DbType.DateTime);

            //Not in heirarchy figure, but valid DB2 data type functions
            //float is same as double
            types.Add("float", DbType.Double);
            //numeric is same as decimal
            types.Add("numeric", DbType.Decimal);
            //long varchar "A LONG VARCHAR value can be up to 32 700 bytes long." ; "A VARCHAR value can be up to 32 672 bytes long.". Almost the same as varchar but
            //long varchar has some special restrictions that make it slightly different than the VARCHAR. more info here: 
            //(http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0008470.htm)
            types.Add("long varchar", DbType.String);

        }

    	/// <summary>
    	/// Create a DB2 database object.
    	/// </summary>
    	/// <param name="provider">The provider for this database.</param>
    	/// <param name="server">The name of the server the database is on.</param>
    	/// <param name="database">The name of the database.</param>
    	/// <param name="credentials">Connect credentials.</param>
    	public WrDB2Database(DbProviderFactory provider, string server, string database, WrCredentials credentials)
        {
            this.provider = provider;
            this.server = server;
            this.database = database;
			this.credentials = credentials;
            syntax = new WrDB2Syntax(provider);
        }
        /// <summary>
        /// The name of the database.
        /// </summary>
        public string Name
        {
            get { return database; }
        }

        /// <summary>
        /// The default schema for all objects in this database. Returns null if there is no default.
        /// </summary>
        public string DefaultSchema
        {
            get
            {
				if (gotSchema)
					return schema;

				try
                {
                    using (DbConnection conn = provider.CreateConnection())
                    {
                    	conn.ConnectionString = ConnectionString(credentials);
                        conn.Open();
                        using (DbCommand cmd = conn.CreateCommand())
                        {
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText =
                            "select CURRENT SCHEMA from SYSIBM.SYSDUMMY1";
                            String rtn = null;
                            using (IDataReader reader = cmd.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    rtn = reader.GetString(0);
                                }
                            }
							gotSchema = true;
							return schema = rtn;
                        }
                    }
                }
                catch (Exception)
                {
					gotSchema = true;
					return null;
                }
                //You can use VALUES(CURRENT SCHEMA) or SELECT CURRENT SCHEMA FROM SYSIBM.SYSDUMMY1 to query this value.
            }
        }

		/// <summary>
		/// All tables in this database. Must call LoadMetadata first.
		/// </summary>
		public WrTable[] Tables
		{
			get { return tables; }
		}

		/// <summary>
		/// All views in this database. Must call LoadMetadata first.
		/// </summary>
		public WrView[] Views
		{
			get { return views; }
		}

		/// <summary>
		/// Return all stored procedures in this database. Must call LoadMetadata first.
		/// </summary>
		public WrStoredProcedure[] StoredProcedures
		{
			get { return procedures; }
		}

		/// <summary>
		/// Loads the metadata for this database.
		/// </summary>
		/// <param name="_credentials">The user credentials to access the database.</param>
		/// <param name="owner">Determines what metadata objects are read and if full metadata is retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		public void LoadMetadata(WrCredentials _credentials, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{
			LoadMetadata(ConnectionString(_credentials), owner, status);
		}

		/// <summary>
		/// Loads the metadata for this database.
		/// </summary>
		/// <param name="connStr">The connection string to access the database.</param>
		/// <param name="owner">Determines what metadata objects are read and if full metadata is retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		public void LoadMetadata(string connStr, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{

			if (status != null)
				status(WrDatabase.TYPE.START, database);

			// first get all columns
			Dictionary<string, List<WrColumn>> allCols = GetColumns(connStr, owner, status);

			tables = GetTables(connStr, owner, allCols, status);

			views = GetViews(connStr, owner, allCols, status);

			procedures = GetStoredProcedures(connStr, owner, status);
			for (int index = 0; index < procedures.Length; index++)
			{
				WrProcParam[] parameters = GetParameters(procedures[index].Name, connStr, owner, status);
				procedures[index] = new WrStoredProcedure(views[index].Schema, views[index].Name, views[index].Description,
				                                          views[index].IsSystem, parameters);
			}
		}

    	/// <summary>
    	/// Return all tables in this database.
    	/// </summary>
    	/// <param name="connStr">The connection string to access the database.</param>
    	/// <param name="owner">Determines what tables are returned and if metadata is also retrieved.</param>
    	/// <param name="allCols">All columns for all tables and views.</param>
    	/// <param name="status">Pass the status of loading the metadata.</param>
    	/// <returns>all tables in this database.</returns>
    	private WrTable[] GetTables(string connStr, WrVendor.ELEM_OWNER owner, Dictionary<string, List<WrColumn>> allCols, WrDatabase.LoadStatus status)
        {
            try
            {
				if (status != null)
					status(WrDatabase.TYPE.TABLES, null);

				using (DbConnection conn = provider.CreateConnection())
                {
                    conn.ConnectionString = connStr;
                    conn.Open();
                    using (IDbCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
						switch (owner & WrVendor.ELEM_OWNER.ALL)
						{
							case WrVendor.ELEM_OWNER.SYSTEM:
								cmd.CommandText =
									"select TABNAME, REMARKS, TABSCHEMA from SYSCAT.TABLES where TYPE in ('T','N') and (TABSCHEMA = 'SYSCAT' OR TABSCHEMA = 'SYSIBM' OR TABSCHEMA = 'SYSIBMADM' OR TABSCHEMA = 'SYSSTAT' OR TABSCHEMA <> 'SYSTOOLS') order by TABSCHEMA, TABNAME";
								break;
							case WrVendor.ELEM_OWNER.USER:
								cmd.CommandText =
									"select TABNAME, REMARKS, TABSCHEMA from SYSCAT.TABLES where TYPE in ('T','N') and TABSCHEMA <> 'SYSCAT' and TABSCHEMA <> 'SYSIBM' AND TABSCHEMA <> 'SYSIBMADM' AND TABSCHEMA <> 'SYSSTAT' AND TABSCHEMA <> 'SYSTOOLS' order by TABSCHEMA, TABNAME";
								break;
							case WrVendor.ELEM_OWNER.USER_OWNED:
								cmd.CommandText =
									"select TABNAME, REMARKS, TABSCHEMA from SYSCAT.TABLES where TYPE in ('T','N') and TABSCHEMA <> 'SYSCAT' and TABSCHEMA <> 'SYSIBM' AND TABSCHEMA <> 'SYSIBMADM' AND TABSCHEMA <> 'SYSSTAT' AND TABSCHEMA <> 'SYSTOOLS' order by TABSCHEMA, TABNAME";
								break;
							default:
								cmd.CommandText =
									"select TABNAME, REMARKS, TABSCHEMA from SYSCAT.TABLES where TYPE in ('T','N') order by TABNAME";
								break;
						}
						List<WrTable> rtn = new List<WrTable>();
                        using (IDataReader reader = cmd.ExecuteReader())
                        {
							while (reader.Read())
							{
								string tblSchema = reader.IsDBNull(2) ? "" : reader.GetString(2).Trim();
								string table = reader.GetString(0).Trim();
								string fullName = MakeFullName(tblSchema, table);
								WrColumn[] cols = allCols.ContainsKey(fullName) ? allCols[fullName].ToArray() : null;
								rtn.Add(new WrTable(tblSchema, table, reader.IsDBNull(1) ? "" : reader.GetString(1), false, cols));

								if (status != null)
									status(WrDatabase.TYPE.TABLE, fullName);
							}
                        }
                        return rtn.ToArray();
                    }
                }
            }
            catch (Exception)
            {
                return new WrTable[0];
            }
        }

		/// <summary>
        /// Return all views in this database.
        /// </summary>
        /// <param name="connStr">The connection string to access the database.</param>
        /// <param name="owner">Determines what views are returned and if metadata is also retrieved.</param>
		/// <param name="allCols">All columns for all tables and views.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		/// <returns>all views in this database.</returns>
		private WrView[] GetViews(string connStr, WrVendor.ELEM_OWNER owner, Dictionary<string, List<WrColumn>> allCols, WrDatabase.LoadStatus status)
		{
			try
			{
				if (status != null)
					status(WrDatabase.TYPE.VIEWS, null);

				using (DbConnection conn = provider.CreateConnection())
				{
					conn.ConnectionString = connStr;
					conn.Open();
					using (DbCommand cmd = conn.CreateCommand())
					{
						cmd.CommandType = CommandType.Text;
						switch (owner & WrVendor.ELEM_OWNER.ALL)
						{
							case WrVendor.ELEM_OWNER.SYSTEM:
								cmd.CommandText =
									"select VIEWNAME, VIEWSCHEMA from SYSCAT.VIEWS where VIEWSCHEMA = 'SYSCAT' OR VIEWSCHEMA = 'SYSIBM' OR VIEWSCHEMA = 'SYSIBMADM' OR VIEWSCHEMA = 'SYSSTAT' OR VIEWSCHEMA = 'SYSTOOLS' OR VIEWSCHEMA = 'SYSCATV82' order by VIEWSCHEMA, VIEWNAME";
								break;
							case WrVendor.ELEM_OWNER.USER:
								cmd.CommandText =
									"select VIEWNAME, VIEWSCHEMA from SYSCAT.VIEWS where VIEWSCHEMA <> 'SYSCAT' and VIEWSCHEMA <> 'SYSIBM' AND VIEWSCHEMA <> 'SYSIBMADM' AND VIEWSCHEMA <> 'SYSSTAT' AND VIEWSCHEMA <> 'SYSTOOLS' AND VIEWSCHEMA <> 'SYSCATV82' order by VIEWSCHEMA, VIEWNAME";
								break;
							case WrVendor.ELEM_OWNER.USER_OWNED:
								cmd.CommandText =
									"select VIEWNAME, VIEWSCHEMA from SYSCAT.VIEWS where VIEWSCHEMA <> 'SYSCAT' and VIEWSCHEMA <> 'SYSIBM' AND VIEWSCHEMA <> 'SYSIBMADM' AND VIEWSCHEMA <> 'SYSSTAT' AND VIEWSCHEMA <> 'SYSTOOLS' AND VIEWSCHEMA <> 'SYSCATV82' order by VIEWSCHEMA, VIEWNAME";
								break;
							default:
								cmd.CommandText = "select VIEWNAME, VIEWSCHEMA from SYSCAT.VIEWS order by VIEWNAME";
								break;
						}
						List<WrView> rtn = new List<WrView>();
						using (IDataReader reader = cmd.ExecuteReader())
						{
							while (reader.Read())
							{
								string tblSchema = reader.IsDBNull(1) ? "" : reader.GetString(1).Trim();
								string table = reader.GetString(0).Trim();
								string fullName = MakeFullName(tblSchema, table);
								WrColumn[] cols = allCols.ContainsKey(fullName) ? allCols[fullName].ToArray() : null;
								rtn.Add(new WrView(tblSchema, table, "", false, cols));

								if (status != null)
									status(WrDatabase.TYPE.VIEW, fullName);
							}
						}
						return rtn.ToArray();
					}
				}
			}
			catch (Exception)
			{
				return new WrView[0];
			}
		}

    	/// <summary>
        /// Return all columns
        /// </summary>
        /// <param name="connStr">The connection string to access the database.</param>
        /// <param name="owner">Determines what columns are returned and if metadata is also retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		/// <returns>all columns in every table and view.</returns>
		private Dictionary<string, List<WrColumn>> GetColumns(string connStr, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
        {
            try
            {
				Dictionary<string, List<WrColumn>> allColumns = new Dictionary<string, List<WrColumn>>();
                using (DbConnection conn = provider.CreateConnection())
                {
                    conn.ConnectionString = connStr;
                    conn.Open();

					// get the FK:PK mappings
                	Dictionary<string, string> fkPk = null;
					if ((owner & WrVendor.ELEM_OWNER.META_DATA) != 0)
					{
						// set the pk-fk mappings
						try
						{
							fkPk = FkPkMappings(conn);
						}
						catch (Exception)
						{
							Trap.trap();
						}
					}
					if (fkPk == null)
						fkPk = new Dictionary<string, string>();

                	using (DbCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText =
						"select TABSCHEMA, TABNAME, COLNAME, REMARKS, TYPENAME from SYSCAT.COLUMNS " +
									"where TABSCHEMA <> 'SYSCAT' and TABSCHEMA <> 'SYSIBM' AND TABSCHEMA <> 'SYSIBMADM' AND TABSCHEMA <> 'SYSSTAT' AND TABSCHEMA <> 'SYSTOOLS' " +
									"order by TABSCHEMA, TABNAME, COLNO";
                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
								string tblSchema = reader.IsDBNull(0) ? "" : reader.GetString(0).Trim();
								string table = reader.GetString(1).Trim();
								string column = reader.GetString(2).Trim();
                            	string desc = reader.IsDBNull(3) ? "" : reader.GetString(3).Trim();
                                string type = reader.GetString(4).ToLower();
								DbType dbType = (DbType)(types.Contains(type) ? types[type] : DbType.Object);
								string fullname = MakeFullName(tblSchema, table);

                            	WrColumn wrCol;
                            	string fullTableColName = fullname + '.' + column;
								if (fkPk.ContainsKey(fullTableColName))
									wrCol = new WrColumn(column, desc, dbType, fkPk[fullTableColName]);
								else
									wrCol = new WrColumn(column, desc, dbType);

								if (allColumns.ContainsKey(fullname))
									allColumns[fullname].Add(wrCol);
								else
								{
									List<WrColumn> cols = new List<WrColumn>();
									cols.Add(wrCol);
									allColumns.Add(fullname, cols);
								}
								if (status != null)
									status(WrDatabase.TYPE.TABLE_COLUMN, fullTableColName);
							}
                        }

                    	return allColumns;
                    }
                }
            }
            catch (Exception)
            {
				return new Dictionary<string, List<WrColumn>>();
            }
        }

		private static string MakeFullName (string schema, string name)
		{
			if (string.IsNullOrEmpty(schema))
				return name;
			return schema + '.' + name;
		}

        /// <summary>
        /// Return all parameters in a stored procedure.
        /// </summary>
        /// <param name="procedure">The name of the stored procedure.</param>
        /// <param name="connStr">The connection string to access the database.</param>
        /// <param name="owner">Determines what columns are returned and if metadata is also retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		/// <returns>all columns in a table or view.</returns>
		public WrProcParam[] GetParameters(string procedure, string connStr, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
        {
            try
            {
                using (DbConnection conn = provider.CreateConnection())
                {
                    conn.ConnectionString = connStr;
                    conn.Open();
                    using (DbCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = "SELECT TYPENAME, PARMNAME, LENGTH, ROWTYPE FROM SYSIBM.SYSROUTINEPARMS WHERE ROUTINENAME = ?";
						SetParameter(cmd, procedure);
                        ArrayList rtn = new ArrayList();
                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                string str = reader.GetString(0).ToLower();
                                DbType typ = (DbType)(types.Contains(str) ? types[str] : DbType.Object);
                                string dir = reader.GetString(3);
                                ParameterDirection direction = ParameterDirection.Input;
                                if (dir.Equals("B"))
                                {
                                    //Both input and output parameter
                                    direction = ParameterDirection.InputOutput;
                                }
                                else if (dir.Equals("C"))
                                {
                                    //Result after casting
                                    direction = ParameterDirection.ReturnValue;
                                }
                                else if (dir.Equals("R"))
                                {
                                    //Result before casting
                                    direction = ParameterDirection.ReturnValue;
                                }
                                else if (dir.Equals("O"))
                                {
                                    //output parameter
                                    direction = ParameterDirection.Output;
                                }
                                else if (dir.Equals("P"))
                                {
                                    //input
                                    direction = ParameterDirection.Input;
                                }
                                ParameterDirection toPass = direction;
                            	string name = reader.GetString(1);
                                rtn.Add(new WrProcParam(name, typ, (int) reader[2], false, toPass));

								if (status != null)
									status(WrDatabase.TYPE.PARAMETER, name);
							}
                        }
                        return (WrProcParam[])rtn.ToArray(typeof(WrProcParam));
                    }
                }
            }
            catch (Exception)
            {
                return new WrProcParam[0];
            }
        }

        /// <summary>
        /// Return all stored procedures in this database.
        /// </summary>
        /// <param name="connStr">The connection string to access the database.</param>
        /// <param name="owner">Determines what stored procedures are returned and if metadata is also retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		/// <returns>all stored procedures in this database.</returns>
		private WrStoredProcedure[] GetStoredProcedures(string connStr, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
        {
            try
            {
				if (status != null)
					status(WrDatabase.TYPE.PROCEDURES, null);

				using (DbConnection conn = provider.CreateConnection())
                {
                    conn.ConnectionString = connStr;
                    conn.Open();
                    using (DbCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
						switch (owner & WrVendor.ELEM_OWNER.ALL)
						{
							case WrVendor.ELEM_OWNER.SYSTEM:
								cmd.CommandText =
									"select PROCNAME, REMARKS, PROCSCHEMA from SYSCAT.PROCEDURES where PROCSCHEMA = 'SYSCAT' or PROCSCHEMA = 'SYSIBM' or PROCSCHEMA = 'SYSIBMADM' or PROCSCHEMA = 'SYSSTAT' or PROCSCHEMA = 'SYSTOOLS' or PROCSCHEMA = 'SQLJ' or PROCSCHEMA = 'SYSPROC' or PROCSCHEMA = 'SYSFUN' order by PROCNAME";
								break;
							case WrVendor.ELEM_OWNER.USER:
								cmd.CommandText =
									"select PROCNAME, REMARKS, PROCSCHEMA from SYSCAT.PROCEDURES where PROCSCHEMA <> 'SYSCAT' and PROCSCHEMA <> 'SYSIBM' AND PROCSCHEMA <> 'SYSIBMADM' AND PROCSCHEMA <> 'SYSSTAT' AND PROCSCHEMA <> 'SYSTOOLS' AND PROCSCHEMA <> 'SQLJ' AND PROCSCHEMA <> 'SYSPROC' AND PROCSCHEMA <> 'SYSFUN' order by PROCNAME";
								break;
							case WrVendor.ELEM_OWNER.USER_OWNED:
								cmd.CommandText =
									"select PROCNAME, REMARKS, PROCSCHEMA from SYSCAT.PROCEDURES where PROCSCHEMA <> 'SYSCAT' and PROCSCHEMA <> 'SYSIBM' AND PROCSCHEMA <> 'SYSIBMADM' AND PROCSCHEMA <> 'SYSSTAT' AND PROCSCHEMA <> 'SYSTOOLS' AND PROCSCHEMA <> 'SQLJ' AND PROCSCHEMA <> 'SYSPROC' AND PROCSCHEMA <> 'SYSFUN' order by PROCNAME";
								break;
							default:
								cmd.CommandText = "select PROCNAME, REMARKS, PROCSCHEMA from SYSCAT.PROCEDURES order by PROCNAME";
								break;
						}
                    	ArrayList rtn = new ArrayList();
                        using (IDataReader reader = cmd.ExecuteReader())
                        {
							while (reader.Read())
							{
								string _schema = reader.GetString(2).Trim();
								string name = reader.GetString(0);
								rtn.Add(new WrStoredProcedure(_schema, name, reader.IsDBNull(1) ? "" : reader.GetString(1), false, null));
								if (status != null)
									status(WrDatabase.TYPE.PROCEDURE, MakeFullName(_schema, name));
							}
                        }
                        return (WrStoredProcedure[])rtn.ToArray(typeof(WrStoredProcedure));
                    }
                }
            }
            catch (Exception)
            {
                return new WrStoredProcedure[0];
            }
        }

        private string ConnectionString(WrCredentials _credentials)
        {
            return WrDB2Vendor.ConnectionString(provider, server, database, _credentials, true);
        }

        private void SetParameter(DbCommand cmd, object value)
        {

            if (value == null)
                value = DBNull.Value;

            DbParameter param = provider.CreateParameter();
            param.ParameterName = "?";
            // DB2 does not handle the types that are less than an Int16 correctly
            if ((value is byte) || (value is bool) || (value is SByte))
                param.Value = Convert.ToInt16(value);
            else
                param.Value = value;

            cmd.Parameters.Add(param);
        }

		// key = FK: schema.table.column; value = PK: schema.table.column
		private Dictionary<string, string> FkPkMappings(DbConnection conn)
		{

			Dictionary<string, string> rtn = new Dictionary<string, string>();
			// get the constraint - if one exists
			using (DbCommand cmd = provider.CreateCommand())
			{
				cmd.Connection = conn;
				cmd.CommandType = CommandType.Text;
				// bugbug - is this correct? PKCOLNAMES is not documented; get self referencing links; multiple pkColumn names doesn't make sense
				cmd.CommandText = "SELECT SYSIBM.SYSTABCONST.NAME, SYSIBM.SYSTABCONST.CONSTRAINTYP, " +
				                  "SYSIBM.SYSKEYCOLUSE.TBCREATOR, SYSIBM.SYSKEYCOLUSE.TBNAME, SYSIBM.SYSKEYCOLUSE.COLNAME, " +
								  "SYSIBM.SYSRELS.REFTBCREATOR, SYSIBM.SYSRELS.REFTBNAME, SYSIBM.SYSRELS.PKCOLNAMES " +
				                  "FROM SYSIBM.SYSTABCONST JOIN SYSIBM.SYSKEYCOLUSE ON (SYSIBM.SYSTABCONST.NAME = SYSIBM.SYSKEYCOLUSE.CONSTNAME) " +
				                  "AND (SYSIBM.SYSTABCONST.CONSTRAINTYP = 'F') JOIN SYSIBM.SYSRELS ON SYSIBM.SYSTABCONST.NAME = SYSIBM.SYSRELS.RELNAME ";

				using (DbDataReader reader = cmd.ExecuteReader())
				{
					while (reader.Read())
					{
						string fkSchema = reader.IsDBNull(2) ? "" : reader.GetString(2).Trim();
						string fkTable = reader.GetString(3).Trim();
						string fkColumn = reader.GetString(4).Trim();
						string pkSchema = reader.IsDBNull(5) ? "" : reader.GetString(2).Trim();
						string pkTable = reader.GetString(6).Trim();
						string pkColumn = reader.GetString(7).Trim();
						string fkFullTableName = MakeFullName(fkSchema, fkTable);
						string pkFullTableName = MakeFullName(pkSchema, pkTable);
						if (fkFullTableName == pkFullTableName)
							continue;
						// we ignore multiple PKs
						if ((pkColumn.IndexOf(' ') != -1) || (pkColumn.IndexOf('\t') != -1))
							continue;
						rtn.Add(fkFullTableName + '.' + fkColumn, pkFullTableName + '.' + pkColumn);
					}
				}
			}

			return rtn;
		}

    	private DbParameter MakeParameter(string name, object value)
        {
            if (value == null)
                value = DBNull.Value;

            DbParameter param = provider.CreateParameter();
            param.ParameterName = name;
            param.Value = value;

            return param;
        }
    }
}
